IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSponsorsByCriteria]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetSponsorsByCriteria]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
*
* Name:			GetSponsorsByCriteria
* Purpose:		Retrieves sponsors based on the passed criteria.
*
* PARAMETERS
* Name    				Description					
* -------------  		-------------------------------------------
* @name              	Name criteria   
* @shortName         	Short name criteria  
* @sponsorTypeId     	Sponsor type criteria 
*
***********************************************************************/
CREATE Procedure [dbo].[GetSponsorsByCriteria] (
		@name		 VARCHAR(75) = null,
		@shortName	 VARCHAR(25) = null,
		@sponsorTypeId	 INT = 0
)
AS

BEGIN

	SET NOCOUNT ON

	SELECT	s.[Id] as SponsorId, 
			s.[Name], 
			s.ShortName, 
			s.[Description], 
			t.[Name] as SponsorType,
			s.DateCreated,
   	        s.RexSponsorId,		
            s.InfoEdSponCode,
            s.PSCustCode,
            s.AdmSponsorTypeId,
			asr.Rate as ChargeRate
	FROM	AdmSponsor s
	INNER JOIN AdmSponsorType T ON
	s.AdmSponsorTypeId = t.[Id]
	INNER JOIN AdmSponsorRate asr
	on asr.Id = T.AdmSponsorRateId
	WHERE
		isnull(s.[Name], '') like isnull(@name, isnull(s.[Name], '')) + '%' AND
		isnull(s.[ShortName], '') like isnull(@shortName, isnull(s.[ShortName], '')) + '%' AND
		(@sponsorTypeId = 0 OR s.AdmSponsortypeId = @sponsorTypeId )
	ORDER BY IsNull(@name,s.ShortName), s.[Name]			

END
GO
